#!/usr/bin/env python

import cx_Oracle
import codecs
import string
import getpass
import sys
import time

dtnow_iso = lambda : time.strftime ("%Y-%m-%dT%H%M%S", time.localtime())
#2008-07-16T19:44:18

def list_columns(conStr={'usr':'', 'pwd':'', 'dsn':''}):
    """ """
    con = cx_Oracle.connect(conStr['usr'], conStr['pwd'], conStr['dsn'])
    cursor = con.cursor()
    
    cursor.execute("""    select   c.OWNER as SCHEMAx
                                 , c.TABLE_NAME as TABLEx
                                 , c.COLUMN_NAME as COLUMNx
                                 , c.COLUMN_ID as COLUMN_IDx
                                 , c.DATA_TYPE as C_DATA_TYPEx
                                 , c.DATA_LENGTH as C_DATA_LENGTHx
                                 , c.CHAR_COL_DECL_LENGTH as C_CHAR_COL_DECL_LENGTHx
                            from all_tab_columns c
                                 left join dba_objects o
                                 on c.OWNER = o.OWNER
                                 and c.TABLE_NAME = o.OBJECT_NAME
                           where o.OBJECT_TYPE = 'TABLE' 
                                 -- exclude system tables
                                 and c.OWNER not in ('SYS', 'SYSMAN', 'MDSYS')
                           order by c.OWNER, c.TABLE_NAME, c.COLUMN_ID, c.COLUMN_NAME   
                                 """)
    result = cursor.fetchall()[:]
    
    o = open('oracle_list_columns_%s.dat'  % (dtnow_iso(), ), 'w')
    #o_err = open('%s_db_err' % (odbc_conn_name), 'w')
    o.write('SCHEMA\tTABLE\tCOLUMN\tcol_data_type\tcol_data_length\tchar_col_decl_length\n'.upper())

    tb_counts = {}

    for r in result:
        schema = r[0]
        table = r[1]
        column = r[2]
        c_data_type = r[4]
        c_data_length = r[5]
        c_char_decl_length = r[6]

        if tb_counts.has_key(schema + '.' + table):
            table_count = tb_counts[schema + '.' + table]
        else:
            table_count = "_"

        #print table_count, schema, table
	if schema in ('GIS_RELEASE', 'SPS') and table_count == '_':
            try:
                q = """select count(1) from %s.%s""" % (schema, table)
                cursor.execute(q)
                r2 = cursor.fetchone()[:]
                table_count = str(r2[0])
                tb_counts[schema + '.' + table] = table_count
            except:
                pass
        #print table_count, schema, table

        o.write("%s\t%s\t%s\t%s\t%s\t%s\t%s\n" % (schema
                , table
                , column
                , c_data_type
                , c_data_length
                , c_char_decl_length
                , table_count))
    
    o.close()

if 0:
    #import cx_Oracle
    #import pepe.p_sql.oracle_sqlplus as osp
    #import getpass

    ip = '127.0.0.1'    # tunneled to remote server or local connection
    #port = 1522         # shifted to tunneled 1522, on 1521 is local XE instance
    #port = 1521
    port = raw_input('Port: ')
    sid = 'dtrslab3'
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)
    #usr = 'vpresovsky'
    usr = raw_input("Input username: ")
    #pwd = '...'
    pwd = getpass.getpass()
    conString = {'usr': usr, 'pwd': pwd, 'dsn': dsn_tns}

    list_columns(conString)
